package com.ncloud.resman.mapper;

import com.alibaba.fastjson.JSONObject;
import com.ncloud.resman.entity.SwitchSubAssign;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by gaowei on 2017/8/30.
 */
@Mapper
public interface SwitchSubAssignMapper {
	@Select("select t1.device_id, t1.sn_num, t1.eqpt_type, t1.type_name,t2.manage_ip,t2.host_name,t2.interface_num,t2.g40_num,t2.module_num,t1.state "
			+ "from res_device_base t1,res_device_switch t2 where t1.device_id=t2.device_id and t1.eqpt_type=2 and t1.device_id = #{deviceId}")
    @Results({@Result(column="device_id",property="deviceId"),@Result(column="sn_num",property="snNum"),
            @Result(column="eqpt_type",property="eqptType"),@Result(column="type_name",property="typeName"),
            @Result(column="manage_ip",property="manageIp"),@Result(column="host_name",property="hostName"),
            @Result(column="interface_num",property="interfaceNum"),@Result(column="g40_num",property="g40Num"),
            @Result(column="module_num",property="moduleNum"),@Result(column="state",property="state")})
    List<JSONObject> getSwitchBaseInfo(long deviceId);//获取交换机信息
	@Select("SELECT CONCAT(t1.sub_id,'') as sub_id,CONCAT(t1.device_id,'') as device_id,t1.sub_type,CONCAT(t1.sub_no,'') as sub_no,CONCAT(ifnull(t2.other_room_id,''),'') as other_room_id,"
			+ "ifnull((select room_name from sys_computer_room t3 where t3.room_id=t2.other_room_id),'') as other_room_name,"
			+ "CONCAT(ifnull(t2.other_cabinet_id,''),'') as other_cabinet_id,"
			+ "ifnull((select cabinet_code from res_cabinet t5 where t5.cabinet_id=t2.other_cabinet_id),'') as other_cabinet_code,"
			+ "CONCAT(ifnull(t2.other_device_id,''),'') as other_device_id,ifnull(t2.other_manage_ip,'') as other_manage_ip,"
			+ "ifnull((select eqpt_type from res_device_base t4 where t4.device_id=t2.other_device_id),'') as eqpt_type,"
			+ "ifnull((select type_name from res_device_base t4 where t4.device_id=t2.other_device_id),'') as type_name,"
			+ "ifnull(t2.other_sub_no,'') as other_sub_no "
			+ "FROM res_device_switch_sub t1 left join res_switch_sub_assign t2 on t1.device_id=t2.device_id and t1.sub_no=t2.sub_no "
            + "where t1.device_id=#{deviceId}")
    @Results({@Result(column="sub_id",property="subId"),@Result(column="device_id",property="deviceId"),
            @Result(column="sub_type",property="subType"),@Result(column="sub_no",property="subNo"),
            @Result(column="other_room_id",property="otherRoomId"),@Result(column="other_room_name",property="otherRoomName"),
            @Result(column="other_cabinet_id",property="otherCabinetId"),@Result(column="other_cabinet_code",property="otherCabinetCode"),
            @Result(column="other_device_id",property="otherDeviceId"),@Result(column="other_manage_ip",property="otherManageIp"),
            @Result(column="other_eqpt_type",property="otherEqptType"),@Result(column="other_type_name",property="otherTypeName"),
            @Result(column="other_sub_no",property="otherSubNo")})
    List<JSONObject> getSwitchSubAssignInfo(long deviceId);//获取交换机接口分配信息
	
	@Select("SELECT device_id FROM res_switch_sub_assign where device_id = #{0} and sub_no = #{1}")
    @Results({@Result(column="device_id",property="deviceId")})
    List<JSONObject> getSwitchSubAssignById(Long deviceId,int subNo);
	
	@Insert("INSERT INTO res_switch_sub_assign (device_id, sub_no, other_room_id, other_cabinet_id, other_device_id, other_manage_ip, other_sub_no, create_user, create_date,remarks) "
			+ "values(#{deviceId},#{subNo},#{otherRoomId},#{otherCabinetId},#{otherDeviceId},#{otherManageIp},#{otherSubNo},#{createUser},#{createDate},#{remarks})")
    int insertSwitchSubAssign(SwitchSubAssign switchSubAssign);
    
	@Update("update res_switch_sub_assign set other_room_id = #{otherRoomId},other_cabinet_id=#{otherCabinetId},other_device_id =#{otherDeviceId},other_manage_ip =#{otherManageIp},"
			+ "other_sub_no = #{otherSubNo}, update_user=#{updateUser},update_date=#{updateDate} where device_id = #{deviceId} and sub_no = #{subNo}")
    int updateSwitchSubAssign(SwitchSubAssign switchSubAssign);
    
	@Delete(value = "delete from res_switch_sub_assign where device_id = #{0} and sub_no = #{1}")
    int deleteSwitchSubAssign(Long deviceId,int subNo);
    @Select("SELECT CONCAT(room_id,'') as room_id, room_name, area_name, country_name, province_name, city_name, addr "
            + "FROM sys_computer_room where room_state = '1'")
    @Results({@Result(column="room_id",property="roomId"),@Result(column="room_name",property="roomName"),
            @Result(column="area_name",property="areaName"),@Result(column="country_name",property="countryName"),
            @Result(column="province_name",property="provinceName"),@Result(column="city_name",property="cityName"),
            @Result(column="addr",property="addr")})
    List<JSONObject> getRoomAll();//获取所有机房信息

    @Select("SELECT CONCAT(cabinet_id,'') as cabinet_id, cabinet_code, room_id, place_num, state FROM res_cabinet "
    		+ "where room_id=#{0} and state=1")
    @Results({@Result(column="cabinet_id",property="cabinetId"),@Result(column="cabinet_code",property="cabinetCode"),
            @Result(column="room_id",property="roomId"),@Result(column="place_num",property="placeNum"),
            @Result(column="state",property="state")})
    List<JSONObject> getCabinetByRoomId(Integer roomId);//根据机房查询其下对应的机柜

    @Select("select t1.device_id, t1.sn_num, t1.room_name, t1.cabinet_code,"
    		+ "t2.eqpt_type, t2.type_name,t3.manage_ip, t2.state "
            + "from res_device_location t1,res_device_base t2,res_device_switch t3 "
            + "where t1.device_id=t2.device_id and t1.device_id=t3.device_id and t2.eqpt_type=2 "
            + "and t2.state=1 and t1.cabinet_id=#{0} ")
    @Results({@Result(column = "device_id", property = "deviceId"),@Result(column="sn_num",property="snNum"),
            @Result(column = "room_name", property = "roomName"),@Result(column="cabinet_code",property="cabinetCode"),
            @Result(column="eqpt_type",property="eqptType"),@Result(column="type_name",property="typeName"),
            @Result(column="manage_ip",property="manageIp"),@Result(column="state",property="state")})
    List<JSONObject> getDeviceSwitchByCabinetId(Long cabinetId);//查询机柜下的所有交换机
    
    @Select("select CONCAT(t1.device_id,'') as device_id, t1.sn_num, t1.room_name, t1.cabinet_code,"
    		+ "t2.eqpt_type, t2.type_name,t3.manage_ip, t2.state "
            + "from res_device_location t1,res_device_base t2,res_device_switch t3 "
            + "where t1.device_id=t2.device_id and t1.device_id=t3.device_id and t2.eqpt_type=2 "
            + "and t2.state=1 and t1.cabinet_id=#{0} "
            + "union all "
            + "select CONCAT(t1.device_id,'') as device_id, t1.sn_num, t1.room_name, t1.cabinet_code,"
    		+ "t2.eqpt_type, t2.type_name,t3.manage_ip, t2.state "
            + "from res_device_location t1,res_device_base t2,res_device_server t3 "
            + "where t1.device_id=t2.device_id and t1.device_id=t3.device_id and t2.eqpt_type=2 "
            + "and t2.state=1 and t1.cabinet_id=#{0} ")
    @Results({@Result(column = "device_id", property = "deviceId"),@Result(column="sn_num",property="snNum"),
            @Result(column = "room_name", property = "roomName"),@Result(column="cabinet_code",property="cabinetCode"),
            @Result(column="eqpt_type",property="eqptType"),@Result(column="type_name",property="typeName"),
            @Result(column="manage_ip",property="manageIp"),@Result(column="state",property="state")})
    List<JSONObject> getManageIpByCabinetId(Long cabinetId);//查询机柜下的所有交换机和服务器的manage_ip
    
    @Select("select CONCAT(id,'') as sub_id,device_id,ip as sub_no from res_device_ip where device_id=#{deviceId}")
    @Results({@Result(column="sub_id",property="subId"),@Result(column="device_id",property="deviceId"),
            @Result(column="sub_no",property="subNo")})
    List<JSONObject> getDeviceIpById(Long deviceId);//根据设备服务器下的接口
    
    @Select("select CONCAT(sub_id,'') as sub_id,CONCAT(device_id,'') as device_id,CONCAT(sub_no,'') as sub_no "
    		+ "from res_device_switch_sub where device_id=#{deviceId}")
    @Results({@Result(column="sub_id",property="subId"),@Result(column="device_id",property="deviceId"),
            @Result(column="sub_no",property="subNo")})
    List<JSONObject> getSwitchSubById(Long deviceId);//根据设备交换机下的接口
}
